import matplotlib.pyplot as plt
import pandas as pd
import sqlite3

# 连接数据库
conn = sqlite3.connect('E:/Procedure/Python/Assignment/Weather/city_weather.db')
# 查询数据
df = pd.read_sql_query("SELECT name, date, high_temp, low_temp FROM weathers", conn)

# 为每个城市生成图表
for city in df['name'].unique():
    city_data = df[df['name'] == city]
    #将温度转为数值，否则无法正确绘制纵坐标
    city_data['high_temp'] = pd.to_numeric(city_data['high_temp'])
    city_data['low_temp'] = pd.to_numeric(city_data['low_temp'])
    plt.figure(figsize=(10, 6))  # 设置图形大小

    plt.plot(city_data['date'], city_data['low_temp'], label='Low Temp')
    plt.plot(city_data['date'], city_data['high_temp'], label='High Temp')
    plt.title(f'Temperature Variation in {city}')
    plt.xlabel('Date')
    plt.ylabel('Temperature (°C)')
    plt.legend()

    plt.savefig(f'static/{city}_temperature_variation.png')  # 保存图形
    plt.close()  # 关闭图形，以便于生成下一个图形

conn.close()